package com.mybatis.generate.provider.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import com.mybatis.generate.generator.GeneratorProperties;
import com.mybatis.generate.provider.db.model.Column;
import com.mybatis.generate.provider.db.model.Table;
import com.mybatis.generate.util.DatabaseDataTypesUtils;

public class DbTableFactory {
	private volatile static DbTableFactory instance;
	private Connection connection;
	
	/**
	 * 构造方法初始化数据库连接
	 */
	private DbTableFactory() {
		init();
	}

	private void init() {
		String driver = GeneratorProperties.getRequiredProperty("jdbc.driver");
		try {
			Class.forName(driver);
		}catch(ClassNotFoundException e) {
			throw new RuntimeException("not found jdbc driver class:["+driver+"]",e);
		}
	}
	
	public static DbTableFactory getInstance() {
		if(instance == null) {
			synchronized(DbTableFactory.class) {
				if(instance == null) {
					instance = new DbTableFactory();
				}
			}
		}
		return instance;
	}

	public List getAllTables() throws SQLException {
		Connection conn = getConnection();
		return getAllTables(conn);
	}
	
	private List getAllTables(Connection conn) throws SQLException {
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rs = dbMetaData.getTables(getCatalog(), getSchema(), null, null);
		List tables = new ArrayList();
		while(rs.next()) {
			Table table = createTable(conn, rs);
			tables.add(table);
		}
		return tables;
	}

	private Table createTable(Connection conn, ResultSet rs) {
		String realTableName = null;
		try {
			ResultSetMetaData rsMetaData = rs.getMetaData();
			String schemaName = rs.getString("TABLE_SCHEM") == null ? "" : rs.getString("TABLE_SCHEM");
			realTableName = rs.getString("TABLE_NAME");
			String tableType = rs.getString("TABLE_TYPE");
			String remarks = rs.getString("REMARKS");
			if(remarks == null && isOracleDataBase()) {
				remarks = getOracleTableComments(realTableName);
			}
			
			Table table = new Table();
			table.setSqlName(realTableName);
			table.setRemarks(remarks);
			
			if("SYNONYM".equals(tableType) && isOracleDataBase()) {
				table.setOwnerSynonymName(getSynonymOwner(realTableName));
			}
			
			retriveTableColumns(table);
//			table.initExportedKeys(conn.getMetaData());
//			table.initImportedKeys(conn.getMetaData());
			return table;
		} catch (SQLException e) {
			throw new RuntimeException("create table object error,tableName:"+realTableName,e);
		}
	}

	//处理（检索）表列
	private void retriveTableColumns(Table table) throws SQLException {
		List primaryKeys = getTablePrimaryKeys(table);
		table.setPrimaryKeyColumns(primaryKeys);
		
		// get the indices and unique columns
		List indices = new LinkedList();
		// maps index names to a list of columns in the index
		Map uniqueIndices = new HashMap();
		// maps column names to the index name.
		Map uniqueColumns = new HashMap();
		ResultSet indexRs = null;
		
		try {
			if(table.getOwnerSynonymName() != null) {
				indexRs = getMetaData().getIndexInfo(getCatalog(), table.getOwnerSynonymName(), table.getSqlName(), false, true);
			}else {
				indexRs = getMetaData().getIndexInfo(getCatalog(), getSchema(), table.getSqlName(), false, true);
			}
			while(indexRs.next()) {
				String columnName = indexRs.getString("COLUMN_NAME");
				if(columnName != null) {
					indices.add(columnName);
				}
				
				//now look for unique columns
				String indexName = indexRs.getString("INDEX_NAME");
				boolean nonUnique = indexRs.getBoolean("NON_UNIQUE");
				
				if(!nonUnique && columnName != null && indexName != null) {
					List list = (List)uniqueColumns.get(indexName);
					if(list == null) {
						list = new ArrayList();
						uniqueColumns.put(indexName, list);
					}
					list.add(columnName);
					uniqueIndices.put(columnName, indexName);
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(null, indexRs);
		}
		
		
		List columns = getTableColumns(table, primaryKeys, indices, uniqueIndices, uniqueColumns);
		
		for (Iterator i = columns.iterator(); i.hasNext();) {
			Column column = (Column)i.next();
			table.addColumn(column);
		}
		
		// In case none of the columns were primary keys, issue a warning.
		if(primaryKeys.size() == 0) {
			System.out.println("WARNING: The JDBC driver didn't report any primary key columns in " + table.getSqlName());
		}
	}

	private List getTableColumns(Table table, List primaryKeys, List indices,
			Map uniqueIndices, Map uniqueColumns) throws SQLException {
		// get the columns
		List columns = new LinkedList();
		ResultSet columnRs = getColumnsResultSet(table);
		while(columnRs.next()) {
			int sqlType = columnRs.getInt("DATA_TYPE");
			String sqlTypeName = columnRs.getString("TYPE_NAME");
			String columnName = columnRs.getString("COLUMN_NAME");
			String columnDefaultValue = columnRs.getString("COLUMN_DEF");
			String remarks = columnRs.getString("REMARKS");
			if(remarks == null && isOracleDataBase()) {
				remarks = getOracleColumnComments(table.getSqlName(), columnName);
			}
			
			// if columnNoNulls or columnNullableUnknown assume "not nullable"
			boolean isNullable = (DatabaseMetaData.columnNullable == columnRs.getInt("NULLABLE"));
			int size = columnRs.getInt("COLUMN_SIZE");
			int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");
			
			boolean isPk = primaryKeys.contains(columnName);
			boolean isIndexed = indices.contains(columnName);
			String uniqueIndex = (String)uniqueIndices.get(columnName);
			List columnsInUniqueIndex = null;
			if(uniqueIndex != null) {
				columnsInUniqueIndex = (List)uniqueColumns.get(uniqueIndex);
			}
			
			boolean isUnique = columnsInUniqueIndex != null && columnsInUniqueIndex.size() == 1;
			Column column = new Column(table, sqlType, sqlTypeName, 
					columnName, size, decimalDigits, isPk, isNullable,
					isIndexed, isUnique, columnDefaultValue, remarks);
			columns.add(column);
			
			if(DatabaseDataTypesUtils.isDate(sqlType, size, decimalDigits)) {
				table.setHasDate(1);
			}
			if(DatabaseDataTypesUtils.isDecimal(sqlType, size, decimalDigits)) {
				table.setHasDecimal(1);
			}
			
			//是否是主键
			if(isUnique && isPk) {
				if(table.getPrimaryKeyColumn() == null) {
					table.setPrimaryKeyColumn(column);
				}
			}
		}
		return columns;
	}

	private String getOracleColumnComments(String table, String column) {
		String sql = "select comments from user_col_comments where table_name='" + table + "' and column_name = '" + column + "'";
		return queryForString(sql);
	}

	private ResultSet getColumnsResultSet(Table table) throws SQLException {
		ResultSet columnRs = null;
		if(table.getOwnerSynonymName() != null) {
			columnRs = getMetaData().getColumns(getCatalog(), table.getOwnerSynonymName(), table.getSqlName(), null);
		}else {
			columnRs = getMetaData().getColumns(getCatalog(), getSchema(), table.getSqlName(), null);
		}
		return columnRs;
	}

	private List getTablePrimaryKeys(Table table) throws SQLException {
		List primaryKeys = new LinkedList();
		ResultSet primaryKeyRs = null;
		if(table.getOwnerSynonymName() != null) {
			primaryKeyRs = getMetaData().getPrimaryKeys(getCatalog(), table.getOwnerSynonymName(), table.getSqlName());
		}else {
			primaryKeyRs = getMetaData().getPrimaryKeys(getCatalog(), getSchema(), table.getSqlName());
		}
		while(primaryKeyRs.next()) {
			String columnName = primaryKeyRs.getString("COLUMN_NAME");
			primaryKeys.add(columnName);
		}
		close(null, primaryKeyRs);
		return primaryKeys;
	}

	private String getSynonymOwner(String synonymName)  {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String ret = null;
		try {
			ps = getConnection().prepareStatement("select table_owner from sys.all_synonyms where table_name=? and owner=?");
			ps.setString(1, synonymName);
			ps.setString(2, getSchema());
	        rs = ps.executeQuery();
	        if (rs.next()) {
	           ret = rs.getString(1);
	        }
	        else {
	           String databaseStructure = getDatabaseStructureInfo();
	           throw new RuntimeException("Wow! Synonym " + synonymName + " not found. How can it happen? " + databaseStructure);
	        }
		} catch (SQLException e) {
			String databaseStructure = getDatabaseStructureInfo();
         	throw new RuntimeException("Exception in getting synonym owner " + databaseStructure);
      } finally {
         close(ps,rs);
      }
      return ret;
   }
	
	private String getDatabaseStructureInfo() {
		ResultSet schemaRs = null;
	    ResultSet catalogRs = null;
	    String nl = System.getProperty("line.separator");
	    StringBuffer sb = new StringBuffer(nl);
	    // Let's give the user some feedback. The exception
	    // is probably related to incorrect schema configuration.
	    sb.append("Configured schema:").append(getSchema()).append(nl);
	    sb.append("Configured catalog:").append(getCatalog()).append(nl);

	    try {
	    	schemaRs = getMetaData().getSchemas();
	        sb.append("Available schemas:").append(nl);
	        while (schemaRs.next()) {
	           sb.append("  ").append(schemaRs.getString("TABLE_SCHEM")).append(nl);
	        }
	    } catch (SQLException e2) {
	        sb.append("  ?? Couldn't get schemas ??").append(nl);
	    } finally {
	         try {
	            schemaRs.close();
	         } catch (Exception e) {
	        	 e.printStackTrace();
	         }
	    }

	    try {
	    	catalogRs = getMetaData().getCatalogs();
	    	sb.append("Available catalogs:").append(nl);
	    	while (catalogRs.next()) {
	    		sb.append("  ").append(catalogRs.getString("TABLE_CAT")).append(nl);
	    	}
	    } catch (SQLException e2) {
	    	sb.append("  ?? Couldn't get catalogs ??").append(nl);
	    } finally {
	    	try {
	    		catalogRs.close();
	    	} catch (Exception e) {
	    		e.printStackTrace();
	    	}
	    }
	    return sb.toString();
    }

	private String getOracleTableComments(String table) {
		String sql = "select comments from user_tab_comments where table_name='" + table + "'";
		return queryForString(sql);
	}

	private String queryForString(String sql) {
		Statement s = null;
		ResultSet rs = null;
		try {
			s = getConnection().createStatement();
			rs = s.executeQuery(sql);
			if(rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(s,rs);
		}
		return null;
	}
	
	private void close(Statement s, ResultSet rs) {
		try {
			if(s != null) {
				s.close();
			}
			if(rs != null) {
				rs.close();
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
	}

	private boolean isOracleDataBase() {
		boolean ret = false;
		try {
			ret = (getMetaData().getDatabaseProductName().toLowerCase().indexOf("oracle") != -1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	private DatabaseMetaData getMetaData() throws SQLException {
		return getConnection().getMetaData();
	}

	public Connection getConnection() throws SQLException {
		if(connection == null || connection.isClosed()) {
			connection = DriverManager.getConnection(GeneratorProperties.getRequiredProperty("jdbc.url"), 
					GeneratorProperties.getRequiredProperty("jdbc.username"), 
					GeneratorProperties.getRequiredProperty("jdbc.password"));
		}
		return connection;
	}

	public String getCatalog() {
		return GeneratorProperties.getNullIfBlank("jdbc.catalog");
	}

	public String getSchema() {
		return GeneratorProperties.getNullIfBlank("jdbc.schema");
	}

	public Table getTable(String tableName) throws SQLException {
		Connection conn = getConnection();
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getTables(getCatalog(), getSchema(), tableName, null);
		while(rs.next()) {
			Table table = createTable(conn, rs);
			return table;
		}
		throw new RuntimeException("not found table with give name:"+tableName);
	}
}
